package com.onemore.task.model;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.voovan.db.JdbcOperate;
import org.voovan.tools.log.Logger;

import com.alibaba.fastjson.JSONObject;
import com.onemore.task.JdbcGlobal;
import com.onemore.task.OneConfig;

public class ModelOneWms extends JdbcGlobal{
	final static private String fields[] = new String[] {"SyncBillId","BILLOUTDATE","QTYIN","SL"};

	static public boolean isNotExist()
	{
		return validateTableExist("ONE_WMS");
	}
	static public boolean isNotExist(String tableName)
	{
		return validateTableExist(tableName);
	}
    static public void createTable() throws SQLException
    {
    	String sql = "CREATE TABLE [dbo].[ONE_WMS] (\r\n" + 
    			"[BillId] varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL DEFAULT '' ,\r\n" + 
    			"[syncbillid] varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL DEFAULT '' ,\r\n" + 
    			"[billdate] char(30) COLLATE Chinese_PRC_CI_AS NOT NULL DEFAULT '' ,\r\n" + 
    			"[QTYIN] int NOT NULL DEFAULT ((0)) ,\r\n" + 
    			")";
//		"PRIMARY KEY ([BillId])\r\n" + 
    	getJdbcOperate().update(sql);
    }
    static public void arrayToTable(List<Map<String, Object>> listWms,String tableName) throws SQLException
	{
		JdbcOperate operate = getJdbcOperate();
		operate.update("truncate table "+tableName);
		for(Map<String, Object> map:listWms) {
			String billId = (String)map.get("BillId");
			String syncbillid = (String)map.get("syncbillid");
			String billdate = (String)map.get("billdate");
			Integer QTYIN = (Integer)map.get("QTYIN");
			operate.update("INSERT INTO "+tableName+" (BillId,syncbillid,billdate,QTYIN)  values('"+billId+"','"+syncbillid+"','"+billdate+"','"+QTYIN+"')");
		}
	}
	static public void importWmsTable() throws SQLException
	{
		arrayToTable(getWmsList(),"ONE_WMS");
	}
    static public List<Map<String, Object>> getWmsList() throws SQLException
    {
    	String sql = "set nocount on if object_id('tempdb..#temp1') is not null Begin drop table #temp1 End Select BillId, Sku, OutLoc, SUM(QtyTotal) As QtyLoc, inLoc Into #temp1 From( Select a.BillId, c.Sku, a.QtyPlan * c.Qty As QtyTotal, a.OutLoc, a.inLoc From TBillMoveLocDirectDet a with(nolock) Inner Join TDefCaseCodeDet c with(nolock) On a.sku = c.CaseBarcode Inner Join TBillMoveLocDirect m with(nolock) On a.BillId = m.BillId Where a.QtyPlan > 0 and a.CasecodeFlag = 1 And (m.Status & 64) > 0 And m.UpLoadFlag = 0 UNION All Select a.BillId, Sku, QtyPlan As QtyTotal, a.OutLoc, a.inLoc From TBillMoveLocDirectDet a with(nolock) Inner Join TBillMoveLocDirect m with(nolock) On a.BillId = m.BillId Where a.QtyPlan > 0 and a.CasecodeFlag <> 1 And (m.Status & 64) > 0 And m.UpLoadFlag = 0) bb Group By BillId, Sku, OutLoc, inLoc Update a Set a.OutLoc = c.ParentWHArea From #temp1 a Inner Join TDefWHLocation c with(nolock) On a.OutLoc = c.WHlocationCode Update a Set a.OutLoc = e.Property4 From #temp1 a inner join TDefWHArea e with(nolock) on e.WHAreaId = a.OutLoc Update a Set a.inloc = d.ParentWHArea From #temp1 a Inner Join TDefWHLocation d with(nolock) On d.WHlocationCode = a.inloc Update a Set a.inloc = f.Property4 From #temp1 a inner join TDefWHArea f with(nolock) on f.WHAreaId = a.inloc \r\n" + 
    			"if object_id('tempdb..#temp2') is not null Begin drop table #temp2 End Select BillId, Sku, OutLoc, SUM(QtyTotal) As QtyLoc, DestCK Into #temp2 From( Select a.BillId, c.Sku, a.QtyPlan * c.Qty As QtyTotal, a.OutLoc ,m.DestCK From TBillOnToLocDet a with(nolock) Inner Join TDefCaseCodeDet c with(nolock) On a.Sku = c.CaseBarcode Inner Join TBillOnToLoc m with(nolock) On a.BillId = m.BillId Where a.QtyPlan> 0 and a.CasecodeFlag = 1 And (m.Status & 64) > 0 And m.UpLoadFlag = 0 UNION All Select a.BillId, Sku, QtyPlan As QtyTotal, OutLoc ,m.DestCK From TBillOnToLocDet a with(nolock) Inner Join TBillOnToLoc m with(nolock) On a.BillId = m.BillId Where a.QtyPlan > 0 And a.CasecodeFlag <> 1 And (m.Status & 64) > 0 And m.UpLoadFlag = 0) bb Group By BillId, Sku, OutLoc, DestCK Update a Set a.OutLoc = c.ParentWHArea From #temp2 a Inner Join TDefWHLocation c with(nolock) On a.OutLoc = c.WHlocationCode Update a Set a.OutLoc = e.Property4 From #temp2 a inner join TDefWHArea e with(nolock) on e.WHAreaId = a.OutLoc Update a Set a.DestCK = d.ParentWHArea From #temp2 a Inner Join TDefWHLocation d with(nolock) On d.WHlocationCode = a.DestCK Update a Set a.DestCK = f.Property4 From #temp2 a inner join TDefWHArea f with(nolock) on f.WHAreaId = a.DestCK\r\n" + 
    			"if object_id('tempdb..#temp3') is not null Begin drop table #temp3 End Select BillId, Sku, OutLoc, SUM(QtyTotal) As QtyLoc, DestCK Into #temp3 From( Select a.BillId, c.Sku, a.QtyPlan * c.Qty As QtyTotal, a.OutLoc, m.DestCK From TBillMoveLocDet a with(nolock) Inner Join TDefCaseCodeDet c with(nolock) On a.sku = c.CaseBarcode Inner Join TBillMoveLoc m with(nolock) On a.BillId = m.BillId Where a.QtyPlan > 0 and a.CasecodeFlag = 1 And m.OrigBillType <> 2 And (m.Status & 64) > 0 And m.UpLoadFlag = 0 UNION All Select a.BillId, Sku, QtyPlan As QtyTotal, OutLoc, m.DestCK From TBillMoveLocDet a with(nolock) Inner Join TBillMoveLoc m with(nolock) On a.BillId = m.BillId Where a.QtyPlan > 0 and a.CasecodeFlag <> 1 And m.OrigBillType <> 2 And (m.Status & 64) > 0 And m.UpLoadFlag = 0) bb Group By BillId, Sku, OutLoc, DestCK Update a Set a.OutLoc = c.ParentWHArea From #temp3 a Inner Join TDefWHLocation c with(nolock) On a.OutLoc = c.WHlocationCode Update a Set a.OutLoc = e.Property4 From #temp3 a inner join TDefWHArea e with(nolock) on e.WHAreaId = a.OutLoc Update a Set a.DestCK = d.ParentWHArea From #temp3 a Inner Join TDefWHLocation d with(nolock) On d.WHlocationCode = a.DestCK Update a Set a.DestCK = f.Property4 From #temp3 a inner join TDefWHArea f with(nolock) on f.WHAreaId = a.DestCK\r\n" + 
    			" select A.BillId,A.syncbillid,A.billdate,SUM(B.QtyIn)as QTYIN from TBillNotePurchase C, TBillStockPurchase A,TBillStockPurchaseDet B WHERE A.BillId=B.BillId and A.OrigBillId=c.BillId AND A.billdate>='2018-01-01' and A.BillType='0' and C.Status='326' and A.SyncBillId not like 'KA3%' GROUP BY A.BillId,A.SyncBillId,A.BillDate \r\n" + 
    			" union all select A.BillId,A.syncbillid,A.billdate,SUM(B.QtyOut)as QTYIN from TBillStockPurchase A,TBillStockPurchaseDet B WHERE A.BillId=B.BillId AND A.billdate>'2018-01-01' and A.BillType='1' and a.Status='66' GROUP BY A.BillId,A.SyncBillId,A.BillDate \r\n" + 
    			" union all select A.syncbillid as BillId,A.BillId as syncbillid,A.billdate,SUM(B.QtyOut)as QTYIN from TBillNoteBatchOut C, TBillBatchOut A,TBillBatchOutDet B WHERE A.BillId=B.BillId and A.OrigBillId=c.BillId AND A.billdate>='2018-01-01' and A.BillType='1' and C.Status='198' and A.SyncBillId not like 'PF%' GROUP BY A.BillId,A.SyncBillId,A.BillDate \r\n" + 
    			" union all select A.BillId,A.syncbillid,A.billdate,SUM(B.QtyOut)as QTYIN from TBillBatchOut A,TBillBatchOutDet B WHERE A.BillId=B.BillId AND A.billdate>='2018-01-01' and A.BillType='0' and A.Status='66' and A.SyncBillId not like 'PF%' GROUP BY A.BillId,A.SyncBillId,A.BillDate\r\n" + 
    			"union all Select a.BillId,'' as syncbillid,'' as billdate, sum( a.QtyLoc) as QTYIN From #temp1 a left Join TDefSku b With(NoLock) On a.sku=b.Sku Where a.OutLoc <> a.inloc group by a.BillId ,a.OutLoc , a.inloc\r\n" + 
    			"union all Select a.BillId,'' as syncbillid,'' as billdate, sum( a.QtyLoc) as QTYIN From #temp2 a left Join TDefSku b With(NoLock) On a.Sku=b.Sku Where a.OutLoc <> a.DestCK group by a.BillId,a.DestCK,a.OutLoc\r\n" + 
    			"union all Select a.BillId,'' as syncbillid,'' as billdate, sum( a.QtyLoc) as QTYIN From #temp3 a left Join TDefSku b With(NoLock) On a.Sku=b.Sku Where a.OutLoc <> a.DestCK group by a.billid,a.OutLoc, a.DestCK";
        List<Map<String, Object>> manyMaps = null;
		manyMaps = getWmsOperate().queryMapList(sql);
		return manyMaps;
    }
}
